package com.wframe.project.former.dataservices.products.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;

public class ProductClassDao extends JBaseDao{

	public ProductClassDao(){
		super("wb_products_class");
	}

	public List<JdataBean> loadClassList() throws Exception{
		return getSession().query("select code,id,pid,pid _parentId,name,photo,orderno,status statusdd from wb_products_class  order by orderno is null asc,orderno,id", null);
	}
	
	public List<JdataBean> loadClassList(String code) throws Exception{
		return getSession().query("select code,id,pid,pid _parentId,name,photo,orderno,status statusdd from wb_products_class where code like ? order by orderno is null asc,orderno,id", new Object[]{code+".%"});
	}
	
	public List<JdataBean> loadClassTree(int pid) throws Exception{
		List<JdataBean> nodelist=new ArrayList<JdataBean>();
		List<JdataBean> list=querySubClassByid(pid);
		for(JdataBean bean:list){
			JdataBean node=new JdataBean();
			node.put("id", bean.getString("id"));
			node.put("text", bean.getString("name"));
			loopSubClassTree(node);
			nodelist.add(node);
		}
		return nodelist;
	}
	
	private void loopSubClassTree(JdataBean node) throws Exception{
		List<JdataBean> nodelist=new ArrayList<JdataBean>();
		List<JdataBean> list=querySubClassByid(node.getInt("id"));
		for(JdataBean bean:list){
			JdataBean subnode=new JdataBean();
			subnode.put("id", bean.getString("id"));
			subnode.put("text", bean.getString("name"));
			loopSubClassTree(subnode);
			nodelist.add(subnode);
		}
		node.put("children", nodelist);
	}
	
	public JdataBean getClassByCode(String code) throws Exception{
		return getSession().getObject("select * from wb_products_class where code=?", new Object[]{code});
	}
	
	public List<JdataBean> querySubClassByid(int pid) throws Exception{
		return getSession().query("select * from wb_products_class where pid=? order by orderno is null asc,orderno,id", new Object[]{pid});
	}
	
	public List<JdataBean> queryModelAttrItem(String attrid) throws Exception{
		return getSession().query("select * from wb_products_attritem where attrid=? and status<>-1 order by orderno,id", new Object[]{attrid});
	}
	
	public List<JdataBean> queryModelAttrItemByCodes(List<String> itemcodes) throws Exception{
		return queryModelAttrItemByCodes(itemcodes.toArray(new String[]{}));
	}
	public List<JdataBean> queryModelAttrItemByCodes(String[] itemcodes) throws Exception{
		String ids="";for(int i=0;i<itemcodes.length;i++)ids+=",?";
		return getSession().query("select * from wb_products_attritem where code in ("+ids.substring(1,ids.length())+") and attrid in (select id from wb_products_attr where type=2)", itemcodes);
	}
	
	public JdataBean getModelAttr(String id) throws Exception{
		return getSession().getObject("select * from wb_products_attr where id=?", new Object[]{id});
	}
	
	public void delModelAttr(String id) throws Exception{
		getSession().update("update wb_products_attr set status=-1 where id=?",new Object[]{id});
		getSession().update("update wb_products_attritem set status=-1 where attrid=?",new Object[]{id});
	}
	
	/**
	 * 
	 * 功能:查询商品分类归属层级<br>
	 * 参数:<br>
	 * 作者:王海锋<br>
	 * 时间:2017-1-14下午11:42:43<br>
	 */
	public List<JdataBean> getProductClassNav(String classcode) throws Exception{
		List<String> codes=new ArrayList<String>();
		String[] code=classcode.split("\\.");
		int len=code.length;
		String str="";
		for(int i=2;i<=len;i++){
			codes.add(StringUtils.join(code, ".", 0, i));
			if(!"".equals(str))str+=",";
			str+="?";
		}
		return getSession().query2("select * from wb_products_class where code in ("+str+") order by code", codes);
	}
	
	
	/**
	 * 
	 * 功能:获得商品分类导航名称<br>
	 * 参数:<br>
	 * 作者:王海锋<br>
	 * 时间:2017-1-10上午12:22:56<br>
	 * @throws Exception 
	 */
	public String getProductClassName(String productid) throws Exception{
		String name="";
		List<JdataBean> clist=getSession().query("select pl.classcode from wb_products_and_class pl where pl.productid=? order by pl.classcode", new Object[]{productid});
		for(JdataBean cbean:clist){
			if(!name.equals(""))name+="；";
			String classCode=cbean.getString("classCode");
			String[] codes=classCode.split("\\.");
			String codestr="";
			for(int i=0;i<codes.length;i++){
				if(!codestr.equals(""))codestr+=",";
				String code="";
				for(int j=0;j<=i;j++){
					code+="."+codes[j];
				}
				codestr+="'"+code.substring(1,code.length())+"'";
			}
			List<JdataBean> list=getSession().query("select name,code from wb_products_class where code in ("+codestr+") order by code", null);
			for(int i=0;i<list.size();i++){
				if(list.get(i).getString("code").split("\\.").length>1)//顶级分类不加入
					name+=list.get(i).getString("name");
			}
		}
		return name;
	}
	
	public PageResult queryFeatured(String code,int sise,int pageno) throws Exception{
		String sql="select t.*,f.id fid,p.unitname,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
				+ " from wb_class_featured f"
				+" inner join wb_products_prices t on t.id=f.priceid"
				+" inner join wb_products p on p.id=f.productid"
				+" left join app_pictures s on s.id=t.photo"//销售属性主图片
				+" left join app_pictures sm on sm.id=p.photo"//商品主图片
				+" where p.status<>-1 and (f.classcode like ? or f.classcode=?)";
		PageResult pr=getSession().query(sql, new Object[]{code+".%",code},sise,pageno);
		List<JdataBean> productlist=pr.getList();
		for(JdataBean product:productlist){
			String photo=product.getString("photoview");
			if(StringUtils.isBlank(photo))photo=product.getString("product_photoview");
			product.put("photo", photo);
			product.put("fullname", product.getString("productname")+" "+product.getDefault("name","").toString());
		}
		return pr;
	}
	
	public List<JdataBean> getFeaturedProducts(String code) throws Exception{
		String sql="select t.*,p.unitname,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
				+ " from wb_class_featured f"
				+" inner join wb_products_prices t on t.id=f.priceid"
				+" inner join wb_products p on p.id=f.productid"
				+" left join app_pictures s on s.id=t.photo"//销售属性主图片
				+" left join app_pictures sm on sm.id=p.photo"//商品主图片
				+" where p.status<>-1 and f.classcode = ?";
		List<JdataBean> productlist=getSession().query(sql, new Object[]{code});
		for(JdataBean product:productlist){
			String photo=product.getString("photoview");
			if(StringUtils.isBlank(photo))photo=product.getString("product_photoview");
			product.put("photo", photo);
			product.put("fullname", product.getString("productname")+" "+product.getDefault("name","").toString());
		}
		return productlist;
	}
	
	public JdataBean getFeatured(String code,String priceid) throws Exception{
		return getSession().getObject("select * from wb_class_featured where classcode=? and priceid=?", new Object[]{code,priceid});
	}
	
	public void saveFeatured(String code,String productid,String priceid) throws Exception{
		JdataBean bean=new JdataBean();
		bean.put("classcode", code);
		bean.put("productid", productid);
		bean.put("priceid", priceid);
		getSession().save("wb_class_featured",bean.getDataMap());
	}
}
